package com.pwt.repository;

import com.pwt.entity.PowerModule;
import com.pwt.model.DiagramModel;
import com.pwt.model.crustallus.GroupModel;
import com.pwt.model.crustallus.OrderModel;
import com.pwt.repository.base.DataRepositoryExtension;
import org.apache.commons.lang3.StringUtils;

import java.util.*;

/**
 * @author dw
 * @date 2021/12/19 17:11
 * @Description
 */
public class PowerModuleRepositoryExtensionImpl extends DataRepositoryExtension<PowerModule, String> implements PowerModuleRepositoryExtension{


    @Override
    public List<OrderModel> queryOrder(String filters, Integer k0010,Integer k1000, String beginTime, String endTime) {
        HashMap<String,Object> map = new HashMap<>();
        String sql = "SELECT \n" +
                "a.K0053,a.k0010,a.K1000,a.minTime,a.maxTime,a.[count],a.qualified,a.disqualification,a.hasQualified, \n" +
                "(case when a.total=0 then 0.00 else Convert(decimal(18,2),a.qualified*1.00/a.total*100) end) as qualifiedRate,\n" +
                "(case when a.total=0 then 0.00 else Convert(decimal(18,2),a.disqualification*1.00/a.total*100) end) as disqualificationRate,\n" +
                "(case when a.total=0 then 0.00 else Convert(decimal(18,2),a.hasQualified*1.00/a.total*100) end) as hasQualifiedRate,\n" +
                "(SELECT t.TETEILNR from  TEIL t where t.TETEIL=a.k1000) as k1001,\n" +
                "(SELECT t.TEBEZEICH from  TEIL t where t.TETEIL=a.k1000) as k1002  "+
                "from (\n" +
                "SELECT K0053,k0010,K1000,begin_time as minTime,end_time maxTime,SUM(ok) qualified,SUM(nok) disqualification,SUM(someok) hasQualified,(SUM(ok)+SUM(nok)+SUM(someok)) total,SUM([count]) [count] from power_module \n" +
                "where  begin_time >= :beginTime and end_time <=:endTime \n" ;
                if(k0010 != null){
                    sql +="  and k0010=:k0010 ";
                    map.put("k0010",k0010);
                }
                if(k1000 != null){
                    sql +=" and k1000=:k1000 ";
                    map.put("k1000",k1000);
                }

                map.put("beginTime",beginTime);
                map.put("endTime",endTime);
                if(StringUtils.isNotBlank(filters)){
                    String[] spilt = filters.split(",");
                    sql+="and  k2005 in(:filters)";
                    map.put("filters", Arrays.asList(spilt));
                }
                sql+=" GROUP BY k0010,K1000,begin_time,end_time,K0053) a order by a.maxTime desc";
        return super.queryCustomBeanListBySql(OrderModel.class,sql,map);
    }


    public DiagramModel queryByK0010(String filters, Set<Integer> k0010, String beginTime, String endTime) {
        HashMap<String,Object> map = new HashMap<>();
        String sql = "SELECT \n" +
                "a.numberOfParts,a.[count],a.qualified,a.disqualification,a.hasQualified, \n" +
                "(case when a.total=0 then 0.00 else Convert(decimal(18,2),a.qualified*1.00/a.total*100) end) as qualifiedRate,\n" +
                "(case when a.total=0 then 0.00 else Convert(decimal(18,2),a.disqualification*1.00/a.total*100) end) as disqualificationRate,\n" +
                "(case when a.total=0 then 0.00 else Convert(decimal(18,2),a.hasQualified*1.00/a.total*100) end) as hasQualifiedRate\n" +
                "from (\n" +
                "SELECT count(distinct K1000) as numberOfParts,SUM(ok) qualified,SUM(nok) disqualification,SUM(someok) hasQualified,(SUM(ok)+SUM(nok)+SUM(someok)) total,SUM([count]) [count] from power_module \n" +
                "where k0010 in(:k0010)   and begin_time >= :beginTime and end_time <=:endTime \n" ;
        map.put("k0010",Arrays.asList(k0010.toArray()));
        map.put("beginTime",beginTime);
        map.put("endTime",endTime);
        if(StringUtils.isNotBlank(filters)){
            String[] spilt = filters.split(",");
            sql+="and  k2005 in(:filters)";
            map.put("filters", Arrays.asList(spilt));
        }
        sql+=") a ";
        return super.queryCustomBeanBySql(DiagramModel.class,sql,map);
    }


    public List<Map<String,Object>> queryK1000GroupBy(String filters, Integer k0010, String beginTime, String endTime) {
        HashMap<String,Object> map = new HashMap<>();
        String sql = "SELECT distinct k1000 as k1000 from power_module \n" +
                "where k0010=:k0010   and begin_time >= :beginTime and end_time <=:endTime \n" ;
        map.put("k0010",k0010);
        map.put("beginTime",beginTime);
        map.put("endTime",endTime);
        if(StringUtils.isNotBlank(filters)){
            String[] spilt = filters.split(",");
            sql+="and  k2005 in(:filters)";
            map.put("filters", Arrays.asList(spilt));
        }
        return super.queryMapListBySql(sql,map);
    }


    public List<GroupModel> queryGroupByMaschine(Set<Integer> k0010,Set<Integer> K1000, String beginTime, String endTime) {
        HashMap<String,Object> map = new HashMap<>();
        String sql = "SELECT \n" +
                "a.k0010 as id,a.minTime,a.maxTime,a.[count],a.qualified,a.disqualification,a.hasQualified, \n" +
                "(case when a.total=0 then 0.00 else Convert(decimal(18,2),a.qualified*1.00/a.total*100) end) as qualifiedRate,\n" +
                "(case when a.total=0 then 0.00 else Convert(decimal(18,2),a.disqualification*1.00/a.total*100) end) as disqualificationRate,\n" +
                "(case when a.total=0 then 0.00 else Convert(decimal(18,2),a.hasQualified*1.00/a.total*100) end) as hasQualifiedRate,\n" +
                "(select MABEZ from MASCHINE where MAMASCHINE=a.k0010)  as name\n" +
                "from (\n" +
                "SELECT k0010,min(begin_time) as minTime,max(end_time) maxTime,SUM(ok) qualified,SUM(nok) disqualification,SUM(someok) hasQualified,(SUM(ok)+SUM(nok)+SUM(someok)) total,SUM([count]) [count] from power_module \n" +
                "where  begin_time >= :beginTime and end_time <=:endTime \n" ;
        map.put("beginTime",beginTime);
        map.put("endTime",endTime);
        if(K1000 != null && K1000.size() > 0 ){
            sql +="  and K1000 in(:K1000) ";
            map.put("K1000",Arrays.asList(K1000.toArray(new Integer[0])));
        }
        if(k0010 != null){
            sql +="  and k0010 in(:k0010) ";
            map.put("k0010",Arrays.asList(k0010.toArray(new Integer[0])));
            sql+="GROUP BY k0010) a order by a.maxTime desc";
        }
        return super.queryCustomBeanListBySql(GroupModel.class,sql,map);
    }

    public List<GroupModel> queryGroupByPart(Set<Integer> k0010,Set<Integer> K1000, String beginTime, String endTime) {
        HashMap<String,Object> map = new HashMap<>();
        String sql = "SELECT \n" +
                "a.K1000 as id,a.minTime,a.maxTime,a.[count],a.qualified,a.disqualification,a.hasQualified, \n" +
                "(case when a.total=0 then 0.00 else Convert(decimal(18,2),a.qualified*1.00/a.total*100) end) as qualifiedRate,\n" +
                "(case when a.total=0 then 0.00 else Convert(decimal(18,2),a.disqualification*1.00/a.total*100) end) as disqualificationRate,\n" +
                "(case when a.total=0 then 0.00 else Convert(decimal(18,2),a.hasQualified*1.00/a.total*100) end) as hasQualifiedRate,\n" +
                "(SELECT t.TEBEZEICH from  TEIL t where t.TETEIL=a.k1000) as name  "+
                "from (\n" +
                "SELECT K1000,min(begin_time) as minTime,max(end_time) maxTime,SUM(ok) qualified,SUM(nok) disqualification,SUM(someok) hasQualified,(SUM(ok)+SUM(nok)+SUM(someok)) total,SUM([count]) [count] from power_module \n" +
                "where  begin_time >= :beginTime and end_time <=:endTime \n" ;
        map.put("beginTime",beginTime);
        map.put("endTime",endTime);
        if(K1000 != null && K1000.size() > 0 ){
            sql +="  and K1000 in(:K1000) ";
            map.put("K1000",Arrays.asList(K1000.toArray(new Integer[0])));
        }
        if(k0010 != null && k0010.size() > 0){
            sql +="  and k0010 in(:k0010) ";
            map.put("k0010",Arrays.asList(k0010.toArray(new Integer[0])));
            sql+="GROUP BY K1000) a order by a.maxTime desc";
        }
        return super.queryCustomBeanListBySql(GroupModel.class,sql,map);
    }
}
